﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Threading;
using QLQTPM_ThuDuc_Group7.DTO;
using DesignLayOut_QLQTPM_Team7.Business;
using Excel = Microsoft.Office.Interop.Excel;


namespace DesignLayOut_QLQTPM_Team7
{
    public partial class Form2 : Form
    {
        private readonly int MADICHVU = 1;
        private readonly int MATENDIADIEM = 2;
        private readonly int SONHA = 3;
        private readonly int MADUONG = 4;
        private readonly int MAPHUONG = 5;
        private readonly int MAQUANHUYEN = 6;
        private readonly int MATINHTHANH = 7;
        private readonly int KINHDO = 8;
        private readonly int VIDO = 9;
        private readonly int CHUTHICH = 10;

        private Excel.Application ExcelObj;
        private Excel.Workbook theWorkbook;
        private Excel.Sheets sheets;
        private Excel.Worksheet worksheet1;
        private int rowStart;
        private int rowEnd;

        public Form2()
        {
            InitializeComponent();
            ExcelObj = null;
            rowStart = 2;
            rowEnd = 12000;
        }

        private void btnBrowse_Click(object sender, EventArgs e)
        {
            OpenFileDialog ofd = new OpenFileDialog();
            ofd.Filter = "Excel File 2007 (*.xlsx)|*.xlsx|Excel File 2003 (*.xls)|*.xls";
            ofd.Title = "Type File";
            if (ofd.ShowDialog() == DialogResult.OK)
            {
                txtInput.Text = ofd.FileName;
            }
        }

        private void btnImport_Click(object sender, EventArgs e)
        {
            if (txtInput.Text == "")
                return;

            
            try
            {
                //Mở đối tượng ứng dụng Excel
                ExcelObj = new Excel.Application();

                //Mở file excel
                theWorkbook = ExcelObj.Workbooks.Open(
                                        txtInput.Text, Type.Missing, Type.Missing, Type.Missing, Type.Missing,
                                        Type.Missing, Type.Missing, Type.Missing, Type.Missing,
                                        Type.Missing, Type.Missing, Type.Missing, Type.Missing,
                                        Type.Missing, Type.Missing);

                //Lấy tất cả worksheets
                sheets = theWorkbook.Worksheets;

                //Lấy worksheet 1
                worksheet1 = (Excel.Worksheet)sheets.get_Item(1);

                Excel.Range range = worksheet1.get_Range("A" + rowStart.ToString(), "J" + rowEnd.ToString());
                Array arrValue = (Array)range.Cells.Value2;
                int colCount = range.Columns.Count;
                int rowCount = range.Rows.Count;

                List<int> lstErrorTenDiaDiem = new List<int>();
                List<DuLieuDTO> lstDuLieu = new List<DuLieuDTO>();
                //Đọc dữ liệu excel và nạp vào lstDuLieu
                for (int i = 1; i <= rowCount; i++)
                {
                    if (arrValue.GetValue(i, MADICHVU) == null || arrValue.GetValue(i, MATENDIADIEM) == null
                        || arrValue.GetValue(i, MADUONG) == null || arrValue.GetValue(i, MAPHUONG) == null
                        || arrValue.GetValue(i, MAQUANHUYEN) == null || arrValue.GetValue(i, MATINHTHANH) == null
                        || arrValue.GetValue(i, KINHDO) == null || arrValue.GetValue(i, VIDO) == null)
                        continue;

                    DuLieuDTO duLieu = new DuLieuDTO();
                    //Mã Dịch Vụ
                    if (arrValue.GetValue(i, MADICHVU) == null)
                        duLieu.maDichVu = 0;
                    else
                        duLieu.maDichVu = Convert.ToInt32(arrValue.GetValue(i, MADICHVU).ToString());

                    //Mã Tên Địa Điểm
                    if (arrValue.GetValue(i, MATENDIADIEM) == null)
                        duLieu.maTenDiaDiem = 0;
                    else
                    {
                        TenDiaDiemDTO tendiadiem = new TenDiaDiemDTO();
                        tendiadiem.TenDiaDiem = Convert.ToString(arrValue.GetValue(i, MATENDIADIEM).ToString());
                        int kq = TenDiaDiemService.ThemTenDiaDiem(tendiadiem);
                        if (kq <= 0)
                        {
                            lstErrorTenDiaDiem.Add(i + 1);
                            continue;
                        }
                        duLieu.maTenDiaDiem = kq;
                        
                    }

                    //Số Nhà
                    if (arrValue.GetValue(i, SONHA) == null)
                        duLieu.soNha = null;
                    else
                        duLieu.soNha = arrValue.GetValue(i, SONHA).ToString();

                    //Mã Đường
                    if (arrValue.GetValue(i, MADUONG) == null)
                        duLieu.maDuong = 0;
                    else
                        duLieu.maDuong = Convert.ToInt32(arrValue.GetValue(i, MADUONG).ToString());

                    //Mã Phường
                    if (arrValue.GetValue(i, MAPHUONG) == null)
                        duLieu.maPhuong = 0;
                    else
                        duLieu.maPhuong = Convert.ToInt32(arrValue.GetValue(i, MAPHUONG).ToString());

                    //Mã Quận Huyện
                    if (arrValue.GetValue(i, MAQUANHUYEN) == null)
                        duLieu.maQuanHuyen = 0;
                    else
                        duLieu.maQuanHuyen = Convert.ToInt32(arrValue.GetValue(i, MAQUANHUYEN).ToString());

                    //Mã Tỉnh Thành
                    if (arrValue.GetValue(i, MATINHTHANH) == null)
                        duLieu.maTinhThanh = 0;
                    else
                        duLieu.maTinhThanh = Convert.ToInt32(arrValue.GetValue(i, MATINHTHANH).ToString());

                    //Kinh Độ
                    if (arrValue.GetValue(i, KINHDO) == null)
                        duLieu.kinhDo = 0;
                    else
                        duLieu.kinhDo = Convert.ToDouble(arrValue.GetValue(i, KINHDO).ToString());

                    //Vĩ Độ
                    if (arrValue.GetValue(i, VIDO) == null)
                        duLieu.viDo = 0;
                    else
                        duLieu.viDo = Convert.ToDouble(arrValue.GetValue(i, VIDO).ToString());

                    //Chú Thích
                    if (arrValue.GetValue(i, CHUTHICH) == null)
                        duLieu.chuThich = null;
                    else
                        duLieu.chuThich = arrValue.GetValue(i, CHUTHICH).ToString();

                    lstDuLieu.Add(duLieu);

                }

                if (lstDuLieu.Count > 0)
                {
                    Thread myThreadImport = new Thread(new ThreadStart(new threadImport(lstDuLieu).importDuLieu));
                    myThreadImport.Start();

                    while (myThreadImport.IsAlive)
                    {
                        Thread.Sleep(20);
                        int rowCurrent = DuLieuService.rowData;
                        int percent = (rowCurrent * 100) / lstDuLieu.Count;
                        proBarStatus.Value = percent;
                    }
                    int n = threadImport.count;
                    if (lstDuLieu.Count - n > 0)
                        MessageBox.Show(n + " dữ liệu import thành công\n" + (lstDuLieu.Count - n) + " dữ liệu import thất bại");
                    else
                        MessageBox.Show(n + " dữ liệu import thành công");
                    proBarStatus.Value = 0;
                }
                else
                {
                    MessageBox.Show("Không có dữ liệu insert trong file excel");
                }
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
            }
            finally
            {
                //Thoát đối tượng Excel
                ExcelObj.Quit();

                //Giải phóng bộ nhớ
                releaseObject(ExcelObj);
                releaseObject(theWorkbook);
                releaseObject(worksheet1);
            }
        }

        //Giải phóng đối tượng COM
        private void releaseObject(object obj)
        {
            try
            {
                System.Runtime.InteropServices.Marshal.ReleaseComObject(obj);
                obj = null;
            }
            catch (Exception ex)
            {
                obj = null;
                MessageBox.Show("Exception Occured while releasing object " + ex.ToString());
            }
            finally
            {
                GC.Collect();
            }
        }
    }
}
